package com.serverlite.framework.dao.core;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.serverlite.framework.dao.C3P0;
import com.serverlite.framework.dao.MySQL;
import com.serverlite.framework.dao.mapping.Mapping;
import com.serverlite.framework.utils.PropertiesUtils;

public class D {
    
    public static boolean showSql = true;

    public static void update(String sql, Object... params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                qRunner.update(con, sql, params);
                
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static List<Map<String, Object>> query(String sql, Object... params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        List<Map<String, Object>> list = null;
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                list = qRunner.query(con, sql, new MapListHandler(), params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
    
    public static Object[] queryArray(String sql, Object... params){
        Object[] array = null;
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                array = qRunner.query(con, sql, new ArrayHandler(), params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return array;
    }
    
    public static List<Object[]> queryArrays(String sql, Object... params){
        List<Object[]> arrays = null;
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                arrays = qRunner.query(con, sql, new ArrayListHandler(), params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return arrays;
    }
    
    public static <T> List<T> queryJava(String sql, Class<T> clazz, Object... params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        List<T> list = null;
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                list = qRunner.query(con, sql, new BeanListHandler<T>(clazz), params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
    
    @SuppressWarnings("unchecked")
    public static <T> T queryJavaOne(String sql, Class<T> clazz, Object... params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        Object obj = null;
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                obj = qRunner.query(con, sql, new BeanHandler<T>(clazz), params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return (T)obj;
    }
    
    public static List<Map<String, Object>> queryAll(String t) {
        return query("select * from " + t);
    }

    public static Page<Map<String, Object>> queryPage(String sql, int pageNo,
            int pageSize, Object... params) {
        Page<Map<String, Object>> page = new Page<Map<String, Object>>();
        Object totalCount = queryScalar("select count(*) from (" + sql
                + ") temp");
        List<Map<String, Object>> data = query(MySQL.pageSql(sql, pageNo,
                pageSize), params);
        page.setPageIndex(pageNo);
        page.setPageSize(pageSize);
        page.setData(data);
        page.setTotalCount(Integer.valueOf(totalCount == null ? "0"
                : totalCount.toString()));
        return page;
    }
    
    public static <T> Page<T> queryJavaPage(String sql, Class<T> clazz, int pageNo,
            int pageSize, Object... params) {
        Page<T> page = new Page<T>();
        Object totalCount = queryScalar("select count(*) from (" + sql
                + ") temp");
        List<T> data = queryJava(MySQL.pageSql(sql, pageNo,
                pageSize), clazz, params);
        page.setPageIndex(pageNo);
        page.setPageSize(pageSize);
        page.setData(data);
        page.setTotalCount(Integer.valueOf(totalCount == null ? "0"
                : totalCount.toString()));
        return page;
    }

    public static Map<String, Object> queryOne(String sql, Object... params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        Map<String, Object> map = null;
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                map = qRunner.query(con, sql, new MapHandler(), params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    public static Object queryScalar(String sql, Object... params) {
        showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
        return queryScalar(sql, showSql, params);
    }
    
    private static Object queryScalar(String sql, boolean showSql, Object... params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        Object o = null;
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                o = qRunner
                        .query(con, sql, new ScalarHandler<Object>(), params);
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return o;
    }

    public static Map<String, Object> queryById(String sql, Object id) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        Map<String, Object> map = null;
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                map = qRunner.query(con, sql, new MapHandler(), id);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    System.out.println("params:[" + id + "]");
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }
    
    @SuppressWarnings("unchecked")
    public static <T> T queryJavaById(Class<T> clazz, Object id) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        String t = Mapping.getTableName(clazz);
        String sql = "select * from " + t + " where id = ?";
        Object obj = null;
        try {
            con = C3P0.getInstance().getConnection();
            obj = qRunner.query(con, sql, new BeanHandler<T>(clazz), id);
            showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
            if(showSql){
                System.out.println("sql:" + sql);
                System.out.println("params:[" + id + "]");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return (T) obj;
    }

    public static void batch(String sql, Object[][] params) {
        Connection con = null;
        QueryRunner qRunner = new QueryRunner();
        try {
            con = C3P0.getInstance().getConnection();
            if (sql != null) {
                qRunner.batch(con, sql, params);
                showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
                if(showSql){
                    System.out.println("sql:" + sql);
                    if (params != null && params.length > 0) {
                        System.out.println("params:" + paramsArrayText(params));
                    }
                }
            } else {
                throw new RuntimeException("sql is null!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DbUtils.closeQuietly(con);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static String paramsArrayText(Object[][] params) {
        StringBuffer ps = new StringBuffer("[");
        for (Object[] pp : params) {
            String ppText = paramsText(pp);
            ps.append(ppText);
            ps.append(",");
        }
        if (ps.lastIndexOf(",") > 0) {
            ps.deleteCharAt(ps.lastIndexOf(","));
        }
        ps.append("]");
        return ps.toString();
    }

    private static String paramsText(Object... params) {
        StringBuffer ps = new StringBuffer("[");
        for (Object p : params) {
            ps.append(p);
            ps.append(",");
        }
        if (ps.lastIndexOf(",") > 0) {
            ps.deleteCharAt(ps.lastIndexOf(","));
        }
        ps.append("]");
        return ps.toString();
    }

    /**
     * 
     * @param in
     * @return in('a','b')
     */
    public static String in(Object[] in) {
        StringBuffer inBuffer = new StringBuffer(" in(");
        for (Object o : in) {
            if (o instanceof String) {
                inBuffer.append("'");
                inBuffer.append(o);
                inBuffer.append("'");
            } else {
                inBuffer.append(o);
            }
            inBuffer.append(",");
        }
        if (inBuffer.lastIndexOf(",") > 0) {
            inBuffer.deleteCharAt(inBuffer.lastIndexOf(","));
        }
        inBuffer.append(")");
        return inBuffer.toString();
    }

    public static List<Map<String, Object>> callProc(String procName,
            Object... params) {
        ResultSet resultSet = null;
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            conn = C3P0.getInstance().getConnection();

            StringBuilder sql = procSqlBuilder(procName, params);
            showSql = Boolean.valueOf(PropertiesUtils.get("show_sql"));
            if(showSql){
                System.out.println("sql: " + sql);
                if (params != null && params.length > 0) {
                    System.out.println("params:" + paramsText(params));
                }
            }

            callStmt = conn.prepareCall(sql.toString());

            setProcParams(callStmt, params);

            callStmt.execute();

            resultSet = callStmt.getResultSet();
            return getResultSet(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (callStmt != null && !callStmt.isClosed()) {
                    DbUtils.closeQuietly(callStmt);
                }
                if (conn != null && !conn.isClosed()) {
                    DbUtils.closeQuietly(conn);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private static void setProcParams(CallableStatement callStmt,
            Object... params) throws SQLException {
        if (params != null && params.length != 0) {
            for (int i = 0; i < params.length; i++) {
                callStmt.setObject(i + 1, params[i]);
            }
        }
    }

    private static StringBuilder procSqlBuilder(String procName,
            Object... params) {
        StringBuilder sql = new StringBuilder("{call ");
        sql.append(procName).append("(");
        if (params != null && params.length != 0) {
            for (int i = 0; i < params.length; i++) {
                sql.append("?,");
            }
            sql.deleteCharAt(sql.length() - 1);
        }
        sql.append(")}");
        return sql;
    }

    private static List<Map<String, Object>> getResultSet(ResultSet rs)
            throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            if (rs != null) {
                ResultSetMetaData rsmd = rs.getMetaData();
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<String, Object>();
                    int columnCount = rsmd.getColumnCount();
                    for (int i = 0; i < columnCount; i++) {
                        String columnName = rsmd.getColumnName(i + 1);
                        map.put(columnName, rs.getObject(i + 1));
                    }
                    list.add(map);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null && !rs.isClosed()) {
                DbUtils.close(rs);
            }
        }
        return list;
    }
}
